{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import sys\n",
    "import os\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "sys.path.insert(0, os.path.realpath('..'))\n",
    "import django\n",
    "from django.db import connections\n",
    "django.setup()\n",
    "from firecares.firestation.models import FireDepartment, FireStation\n",
    "from IPython.display import display\n",
    "\n",
    "pd.set_option('display.max_columns', None)\n",
    "\n",
    "# Note: you'll need to move the predictions csv into /firecares, see s3://firecares-share/predictions.2015.csv\n",
    "\n",
    "df = pd.read_csv('/firecares/predictions.2015.csv')\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Count of departments in csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "cols = ['lr_fire', 'lr_size_2', 'lr_size_3', 'mr_fire', 'mr_size_2', 'mr_size_3', 'h.fire', 'h.size2', 'h.size3']\n",
    "\n",
    "fd_id = 75500\n",
    "\n",
    "# for xx.size2 (beyond room + beyond floor) you would want\n",
    "# Sum( xx.fire * xx.size2 ) / sum( xx.fire )\n",
    "# And for xx.size3 (beyond structure) you would want\n",
    "# Sum( xx.fire * xx.size2 * xx.size3 ) / sum( xx. fire * xx.size2 )\n",
    "df['lr_beyond_room'] = df[df['fd_id'] == fd_id].apply(lambda row: row['lr_fire'] * row['lr_size_2'], axis=1)\n",
    "df['lr_beyond_structure'] = df[df['fd_id'] == fd_id].apply(lambda row: row['lr_fire'] * row['lr_size_2'] * row['lr_size_3'], axis=1)\n",
    "df['mr_beyond_room'] = df[df['fd_id'] == fd_id].apply(lambda row: row['mr_fire'] * row['mr_size_2'], axis=1)\n",
    "df['mr_beyond_structure'] = df[df['fd_id'] == fd_id].apply(lambda row: row['mr_fire'] * row['mr_size_2'] * row['mr_size_3'], axis=1)\n",
    "df['hr_beyond_room'] = df[df['fd_id'] == fd_id].apply(lambda row: row['h.fire'] * row['h.size2'], axis=1)\n",
    "df['hr_beyond_structure'] = df[df['fd_id'] == fd_id].apply(lambda row: row['h.fire'] * row['h.size2'] * row['h.size3'], axis=1)\n",
    "\n",
    "cols2 = ['lr_beyond_room', 'lr_beyond_structure', 'mr_beyond_room', 'mr_beyond_structure', 'hr_beyond_room', 'hr_beyond_structure']\n",
    "\n",
    "sums = df[df['fd_id'] == fd_id].groupby(['fd_id', 'state']).sum()\n",
    "display(sums[cols])\n",
    "display(sums[cols2])\n",
    "\n",
    "print '% of low risk beyond room/floor fires: {}'.format(sums['lr_beyond_room'][0] / sums['lr_fire'][0])\n",
    "print '% of low risk beyond structure fires: {}'.format(sums['lr_beyond_structure'][0] / sums['lr_beyond_room'][0])\n",
    "print '% of medium risk beyond room/floor fires: {}'.format(sums['mr_beyond_room'][0] / sums['mr_fire'][0])\n",
    "print '% of medium risk beyond structure fires: {}'.format(sums['mr_beyond_structure'][0] / sums['mr_beyond_room'][0])\n",
    "print '% of high risk beyond room/floor fires: {}'.format(sums['hr_beyond_room'][0] / sums['h.fire'][0])\n",
    "print '% of high risk beyond structure fires: {}'.format(sums['hr_beyond_structure'][0] / sums['hr_beyond_room'][0])\n",
    "\n",
    "#df[df['fd_id'] == 93345]\n",
    "#lr_size2 = df[['lr_fire', 'lr_size_2']].apply(lambda row: row['lr_fire'] * row['lr_size_2'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "cols2 = ['lr_size_2', 'mr_size_2', 'h.size2', 'lr_size_3', 'mr_size_3', 'h.size3']\n",
    "\n",
    "df[df['fd_id'] == 93345].groupby(['fd_id', 'state']).sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### CSV counts vs FireCARES count"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "db = len(FireDepartment.objects.count())\n",
    "csv = len(df.groupby(['fd_id', 'state'])[cols].sum())\n",
    "(db, csv, csv/float(db))  # hit rate"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Community Assessment vs Performance Score"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Has no community fires assessment, but has deaths/injury assessment\n",
    "q = \"\"\"\n",
    "select department_id,\n",
    "    case when level = 0 then 'ALL LEVELS'\n",
    "        when level = 1 then 'LOW'\n",
    "        when level = 2 then 'MEDIUM'\n",
    "        when level = 4 then 'HIGH'\n",
    "        when level = 5 then 'UNKNOWN'\n",
    "    end\n",
    "from firestation_firedepartmentriskmodels\n",
    "where risk_model_fires is null and (risk_model_injuries is not null or risk_model_deaths is not null)\n",
    "\"\"\"\n",
    "pd.read_sql_query(q, connections['default']).to_csv('/tmp/only_deaths_injuries.csv')\n",
    "#pd.read_sql_query(q, connections['default'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Has performance score, but is missing one or more community assessments\n",
    "q = \"\"\"\n",
    "select department_id,\n",
    "    case when level = 0 then 'ALL LEVELS'\n",
    "        when level = 1 then 'LOW'\n",
    "        when level = 2 then 'MEDIUM'\n",
    "        when level = 4 then 'HIGH'\n",
    "        when level = 5 then 'UNKNOWN'\n",
    "    end,\n",
    "    risk_model_injuries is null as injuries_empty,\n",
    "    risk_model_deaths is null as deaths_empty,\n",
    "    risk_model_fires is null as fires_empty,\n",
    "    risk_model_fires_size1 is null as fires_size1_empty,\n",
    "    risk_model_fires_size2 is null as fires_size2_empty,\n",
    "    dist_model_score\n",
    "from firestation_firedepartmentriskmodels\n",
    "where\n",
    "    dist_model_score is not null\n",
    "        and (risk_model_injuries is null\n",
    "            or risk_model_deaths is null\n",
    "            or risk_model_fires is null\n",
    "            or risk_model_fires_size1 is null\n",
    "            or risk_model_fires_size2 is null)\n",
    "        and level != 5\n",
    "order by department_id\n",
    "\"\"\"\n",
    "pd.read_sql_query(q, connections['default']).to_csv('/tmp/score_but_no_assessment.csv')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Django Shell-Plus",
   "language": "python",
   "name": "django_extensions"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
